﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace BuildForm
{
    public partial class Employees : Form
    {
        SqlConnection con;
        SqlCommand cmd;
        DataSet ds = new DataSet();
        SqlDataAdapter da;
        public Employees()
        {
            try
            {
                InitializeComponent();
                con = new SqlConnection();
                con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                Start();
                LoadEmployeesInfo();
                cbMgrID.SelectedIndex = -1;
            }
            catch (Exception ex)
            {
                this.Close();
                throw ex;
            }
        }

        #region My Code

        void Start()
        {
            try
            {
                btnUpdate.Enabled = false;
                btnDelete.Enabled = false;
                btnReload.Enabled = false;
                cbSearch.SelectedIndex = 0;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

        bool ValidateInput()
        {
            bool error = false;
            try
            {
                if (txtLastName.Text.Trim().Length == 0)
                {
                    errorProvider1.SetError(txtLastName, "Input Last Name");
                    error = true;
                }
                if (txtFirstName.Text.Trim().Length == 0)
                {
                    errorProvider1.SetError(txtFirstName, "Input First Name");
                    error = true;
                }
                if (cbTitle.SelectedIndex < 0)
                {
                    errorProvider1.SetError(cbTitle, "Choose Title");
                    error = true;
                }
                if (cbTitleOfCoursety.SelectedIndex < 0)
                {
                    errorProvider1.SetError(cbTitleOfCoursety, "Choose Title of Courtesy");
                    error = true;
                }

                #region birthdate
                if (DateTime.Now.Year - dtpBirthDate.Value.Year < 18)
                {
                    errorProvider1.SetError(dtpBirthDate, "Under 18");
                    error = true;
                }
                if (DateTime.Now.Year - dtpBirthDate.Value.Year == 18)
                {
                    if (DateTime.Now.Month > dtpBirthDate.Value.Month)
                    {
                        errorProvider1.SetError(dtpBirthDate, "Under 18");
                        error = true;
                    }
                }
                if (DateTime.Now.Year - dtpBirthDate.Value.Year == 18)
                {
                    if (DateTime.Now.Month == dtpBirthDate.Value.Month)
                    {
                        if (DateTime.Now.Day > dtpBirthDate.Value.Day)
                        {
                            errorProvider1.SetError(dtpBirthDate, "Under 18");
                            error = true;
                        }
                    }
                }
                #endregion

                #region hiredate
                if (dtpHireDate.Value.Year - dtpBirthDate.Value.Year < 18)
                {
                    errorProvider1.SetError(dtpHireDate, "Under 18");
                    error = true;
                }
                if (dtpHireDate.Value.Year - dtpBirthDate.Value.Year == 18)
                {
                    if (dtpHireDate.Value.Month < dtpBirthDate.Value.Month)
                    {
                        errorProvider1.SetError(dtpHireDate, "Under 18");
                        error = true;
                    }
                }
                if (dtpHireDate.Value.Year - dtpBirthDate.Value.Year == 18)
                {
                    if (dtpHireDate.Value.Month == dtpBirthDate.Value.Month)
                    {
                        if (dtpHireDate.Value.Day < dtpBirthDate.Value.Day)
                        {
                            errorProvider1.SetError(dtpHireDate, "Under 18");
                            error = true;
                        }
                    }
                }

                #endregion



                if (txtAddress.Text.Trim().Length == 0)
                {
                    errorProvider1.SetError(txtAddress, "Input Address");
                    error = true;
                }
                if (txtCity.Text.Trim().Length == 0)
                {
                    errorProvider1.SetError(txtCity, "Input CIty");
                    error = true;
                }
                if (mtbPostalCode.Text != "")
                {
                    if (mtbPostalCode.MaskFull == false)
                    {
                        errorProvider1.SetError(mtbPostalCode, "No full");
                        error = true;
                    }
                }
                if (cbCountry.SelectedIndex < 0)
                {
                    errorProvider1.SetError(cbCountry, "Choose Country");
                    error = true;
                }
                if (mtbPhone.Text.Trim() == "(   )    -")
                {
                    errorProvider1.SetError(mtbPhone, "Input Phone");
                    error = true;
                }
                if (mtbPhone.MaskCompleted == false)
                {
                    errorProvider1.SetError(mtbPhone, "Input Phone");
                    error = true;
                }
                if (cbMgrID.SelectedIndex==-1)
                {
                    if (ckbManagerName.Checked==false)
                    {
                        errorProvider1.SetError(cbMgrID, "Please choose");
                        error = true;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            if (error == true)
            {
                return false;
            }
            return true;

        }

        void LoadEmployeesInfo()
        {
            try
            {
                gvEmployees.Rows.Clear();
                con.Open();
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * FROM HR.Employees";
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    gvEmployees.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(),
                        dr[5].ToString(), dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString(), dr[11].ToString(), dr[12].ToString(),
                        dr[13].ToString());
                }
                dr.Close();
            }
            catch (Exception e)
            {

                MessageBox.Show(e.Message);
            }
            finally
            {
                con.Close();
            }

        }

        void AddEmployees()
        {
            try
            {
                con.Open();
                cmd = new SqlCommand();
                cmd.Connection = con;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_Employees_Insert";

                SqlParameter param = new SqlParameter("@lastname", SqlDbType.NVarChar);
                param.Size = 20;
                param.Value = txtLastName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@firstname", SqlDbType.NVarChar);
                param.Size = 10;
                param.Value = txtFirstName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@title", SqlDbType.NVarChar);
                param.Size = 30;
                param.Value = cbTitle.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@titleofcourtesy", SqlDbType.NVarChar);
                param.Size = 25;
                param.Value = cbTitleOfCoursety.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@birthdate", SqlDbType.DateTime);
                param.Value = dtpBirthDate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@hiredate", SqlDbType.DateTime);
                param.Value = dtpHireDate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@address", SqlDbType.NVarChar);
                param.Size = 60;
                param.Value = txtAddress.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@city", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = txtCity.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@region", SqlDbType.NVarChar);
                param.Size = 15;
                string region = "";
                region = txtRegion.Text.Trim();
                if (region == "")
                {
                    param.Value = DBNull.Value;
                }
                else
                {
                    param.Value = txtRegion.Text;
                }
                cmd.Parameters.Add(param);

                param = new SqlParameter("@postalcode", SqlDbType.NVarChar);
                param.Size = 10;
                string postalcode = "";
                postalcode = mtbPostalCode.Text;
                if (postalcode == "")
                {
                    param.Value = DBNull.Value;
                }
                else
                {
                    param.Value = mtbPostalCode.Text;
                }
                cmd.Parameters.Add(param);

                param = new SqlParameter("@country", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = cbCountry.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@phone", SqlDbType.NVarChar);
                param.Size = 24;
                param.Value = mtbPhone.Text;
                cmd.Parameters.Add(param);

                if (ckbManagerName.Checked == false)
                {
                    param = new SqlParameter("@mgrid", SqlDbType.Int);
                    param.Value = (Int32)cbMgrID.SelectedValue;
                    cmd.Parameters.Add(param);
                }
                else
                {
                    param = new SqlParameter("@mgrid", SqlDbType.Int);
                    param.Value = DBNull.Value;
                    cmd.Parameters.Add(param);
                }

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close(); 
            }

        }

        void UpdateEmployees()
        {
            try
            {
                con.Open();
                cmd = new SqlCommand();
                cmd.Connection = con;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_Employees_Update";

                SqlParameter param = new SqlParameter("@empid", SqlDbType.Int);
                param.Value = txtEmpID.Text;
                cmd.Parameters.Add(param);


                param = new SqlParameter("@lastname", SqlDbType.NVarChar);
                param.Size = 20;
                param.Value = txtLastName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@firstname", SqlDbType.NVarChar);
                param.Size = 10;
                param.Value = txtFirstName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@title", SqlDbType.NVarChar);
                param.Size = 30;
                param.Value = cbTitle.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@titleofcourtesy", SqlDbType.NVarChar);
                param.Size = 25;
                param.Value = cbTitleOfCoursety.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@birthdate", SqlDbType.DateTime);
                param.Value = dtpBirthDate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@hiredate", SqlDbType.DateTime);
                param.Value = dtpHireDate.Value.ToShortDateString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@address", SqlDbType.NVarChar);
                param.Size = 60;
                param.Value = txtAddress.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@city", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = txtCity.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@region", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = txtRegion.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@postalcode", SqlDbType.NVarChar);
                param.Size = 10;
                param.Value = mtbPostalCode.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@country", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = cbCountry.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@phone", SqlDbType.NVarChar);
                param.Scale = 24;
                param.Value = mtbPhone.Text;
                cmd.Parameters.Add(param);

                if (ckbManagerName.Checked == false)
                {
                    param = new SqlParameter("@mgrid", SqlDbType.Int);
                    param.Value = (Int32)cbMgrID.SelectedValue;
                    cmd.Parameters.Add(param);
                }
                else
                {
                    param = new SqlParameter("@mgrid", SqlDbType.Int);
                    param.Value = DBNull.Value;
                    cmd.Parameters.Add(param);
                }

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close(); 
            }
        }

        void DeleteEmployees()
        {
            try
            {
                con.Open();
                cmd = new SqlCommand();
                cmd.Connection = con;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.sp_Employees_Delete";

                SqlParameter param = new SqlParameter("@empid", SqlDbType.Int);
                param.Value = txtEmpID.Text;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                MessageBox.Show("Complete!");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
            

        }

        void ClearInputEmployees()
        {
            try
            {
                txtEmpID.Clear();
                txtLastName.Clear();
                txtFirstName.Clear();
                cbTitle.SelectedIndex = -1;
                cbTitleOfCoursety.SelectedIndex = -1;
                dtpBirthDate.Value = DateTime.Now;
                dtpHireDate.Value = DateTime.Now;
                txtAddress.Clear();
                txtCity.Clear();
                txtRegion.Clear();
                mtbPostalCode.Text = "";
                cbCountry.SelectedIndex = -1;
                mtbPhone.Text = "";
                cbMgrID.SelectedIndex = -1;
                ckbManagerName.Checked = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        void getDatasource_mgrID()
        {
            try
            {
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT empid,firstname + ' ' + lastname AS fullname FROM HR.Employees";
                da = new SqlDataAdapter(cmd);
                da.FillSchema(ds, SchemaType.Source, "HR.Employees");
                da.Fill(ds, "HR.Employees");
                DataTable dt = ds.Tables["HR.Employees"];
                da.Fill(dt);
                cbMgrID.DataSource = dt;
                cbMgrID.DisplayMember = "fullname";
                cbMgrID.ValueMember = "empid";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        void SearchEmployees()
        {
            bool checkS = true;
            try
            {
                gvEmployees.Rows.Clear();
                con.Open();
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                if (cbSearch.SelectedIndex == 1)
                {
                    cmd.CommandText = string.Format("SELECT * FROM HR.Employees WHERE firstname + ' ' + lastname LIKE '%{0}%'", txtSearch.Text);
                }
                else
                {
                    if (cbSearch.SelectedIndex == 2)
                    {
                        cmd.CommandText = string.Format("SELECT * FROM HR.Employees WHERE title LIKE '%{0}%'", txtSearch.Text);
                    }
                }
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    gvEmployees.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(),
                        dr[5].ToString(), dr[6].ToString(), dr[7].ToString(), dr[8].ToString(), dr[9].ToString(), dr[10].ToString(), dr[11].ToString(), dr[12].ToString(),
                        dr[13].ToString());
                }

                dr.Close();
            }
            catch (Exception ex)
            {
                checkS = false;
                MessageBox.Show("Please choose search by");
                
            }
            finally
            {
                con.Close();
            }
            try
            {
                if (checkS == false)
                {
                    LoadEmployeesInfo();
                }
            }
            catch (Exception ex)
            {
                
                throw ex;
            }

        }

        void SelectRow()
        {
            try
            {
                if (gvEmployees.SelectedRows.Count > 0)
                {
                    btnDelete.Enabled = true;
                    btnUpdate.Enabled = true;
                    DataGridViewRow r = gvEmployees.SelectedRows[0];
                    txtEmpID.Text = r.Cells[0].Value.ToString();
                    txtLastName.Text = r.Cells[1].Value.ToString();
                    txtFirstName.Text = r.Cells[2].Value.ToString();
                    cbTitle.Text = r.Cells[3].Value.ToString();
                    cbTitleOfCoursety.Text = r.Cells[4].Value.ToString();
                    DateTime dt;
                    DateTime.TryParse(r.Cells[5].Value.ToString(), out dt);
                    dtpBirthDate.Value = dt;
                    DateTime.TryParse(r.Cells[6].Value.ToString(), out dt);
                    dtpHireDate.Value = dt;
                    txtAddress.Text = r.Cells[7].Value.ToString();
                    txtCity.Text = r.Cells[8].Value.ToString();
                    txtRegion.Text = r.Cells[9].Value.ToString();
                    mtbPostalCode.Text = r.Cells[10].Value.ToString();
                    cbCountry.Text = r.Cells[11].Value.ToString();
                    mtbPhone.Text = r.Cells[12].Value.ToString();
                    if (r.Cells[13].Value.ToString() != "")
                    {
                        cbMgrID.Enabled = true;
                        cbMgrID.SelectedValue = r.Cells[13].Value.ToString();
                    }
                    else
                    {
                        cbMgrID.Enabled = false;
                        ckbManagerName.Checked = true;
                    }
                }
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
        }

        #endregion

        #region Event
        private void btnReload_Click(object sender, EventArgs e)
        {
            try
            {
                Start();
                txtSearch.Text = "";
                LoadEmployeesInfo();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);   
            }
        }

        private void Employees_Load(object sender, EventArgs e)
        {
            try
            {
                getDatasource_mgrID();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            try
            {
                errorProvider1.Clear();
                gvEmployees.ClearSelection();
                ClearInputEmployees();
                btnAdd.Enabled = true;
                btnDelete.Enabled = false;
                btnUpdate.Enabled = false;
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
        }



        private void btnAdd_Click(object sender, EventArgs e)
        {
            errorProvider1.Clear();
            bool check = true;
            if (ValidateInput() == false)
            {
                return;
            }
            else
            {
                try
                {
                    AddEmployees();
                    gvEmployees.Rows.Clear();
                    LoadEmployeesInfo();
                }
                catch (Exception ex)
                {

                    MessageBox.Show("Can't Add");
                    check = false;
                }
                if (check == true)
                {
                    MessageBox.Show("Add Successfull!"); 
                }
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            try
            {
                DialogResult rs = MessageBox.Show("Are you sure?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (rs == DialogResult.Yes)
                {
                    if (gvEmployees.SelectedRows.Count > 0)
                    {
                        DeleteEmployees();
                        gvEmployees.Rows.Clear();
                        LoadEmployeesInfo();
                        ClearInputEmployees();
                    }
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show("Can't Delete");
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            {
                DialogResult rs = MessageBox.Show("Are you sure?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (rs == DialogResult.Yes)
                {
                    if (gvEmployees.SelectedRows.Count > 0)
                    {
                        errorProvider1.Clear();
                        if (ValidateInput() == false)
                        {
                            return;
                        }
                        else
                        {
                            UpdateEmployees();
                            gvEmployees.Rows.Clear();
                            LoadEmployeesInfo();
                            getDatasource_mgrID();
                            MessageBox.Show("Complete!");
                        }
                    }
                }
            }
            catch (Exception)
            {

                MessageBox.Show("Can't Update");
            }
        }

        private void gvEmployees_SelectionChanged(object sender, EventArgs e)
        {
            try
            {
                if (gvEmployees.SelectedRows.Count > 0)
                {
                    ckbManagerName.Checked = false;
                    SelectRow();
                    btnAdd.Enabled = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can't Select");
            }
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                SearchEmployees();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            btnReload.Enabled = true;
        }

        #endregion









    }

}
